import pandas as pd

import talib
from sqlalchemy import create_engine
TABLE_FUND_DAY_K_DATA = "shenge_money_fund_day_k_data"
import mplfinance as mpf
import tqdm
import time
import requests

def connect_db():
    connect_info = 'mysql+pymysql://paqi:chs518518!@rm-bp1v9plylv4340as7qo.mysql.rds.aliyuncs.com:3306/paqi?charset=utf8'
    engine = create_engine(connect_info)
    return engine

def LOW(df,n):
    mins = []
    for index,row in df.iterrows():
        tdate = row['tdate']
        dff = df[df['tdate']<=tdate]
        if len(dff) >= n:
            dff = dff.tail(n)
        min = dff['low'].min()
        mins.append(min)
    return pd.Series(mins).astype('float')

def HIGH(df,n):
    maxs = []
    for index,row in df.iterrows():
        tdate = row['tdate']
        dff = df[df['tdate']<=tdate]
        if len(dff) >= n:
            dff = dff.tail(n)
        max = dff['high'].max()
        maxs.append(max)
    return pd.Series(maxs).astype('float')

def judeBuyPoint(ktype,df_cp):
    if (ktype == 101 or ktype==102) and len(df_cp) >= 5:
        df_judge = df_cp.tail(5)
        lastFivDatas = []
        for index, row in df_judge.iterrows():
            lastFivDatas.append(dict(row))

        isUp = False
        isDown = False
        if lastFivDatas[4]["D"] <= lastFivDatas[4]["K"] or lastFivDatas[3]["D"] <= lastFivDatas[3]["K"]:
            isUp = True
        if lastFivDatas[0]["D"] > lastFivDatas[0]["K"] and lastFivDatas[1]["D"] > lastFivDatas[1]["K"] and \
                lastFivDatas[2]["D"] > lastFivDatas[2]["K"]:
            isDown = True
        if lastFivDatas[4]["D"] > lastFivDatas[4]["K"] and lastFivDatas[3]["D"] < lastFivDatas[3]["K"]:
            isUp = False
        return isDown and isUp
    else:
        if (ktype == 103) and len(df_cp) >= 3:
            df_judge = df_cp.tail(3)
            lastFivDatas = []
            for index, row in df_judge.iterrows():
                lastFivDatas.append(dict(row))

            isUp = False
            isDown = False
            if lastFivDatas[2]["D"] < lastFivDatas[2]["K"]:
                isUp = True
            if lastFivDatas[0]["D"] > lastFivDatas[0]["K"] and lastFivDatas[1]["D"] > lastFivDatas[1]["K"]:
                isDown = True
            return isDown and isUp
        else:
            return False


def getRealTimeFundData(code):
    code = str(code)
    if code[0] =='5':
        code = "sh"+code
    
    if code[0] == '1':
        code = "sz"+code
    r2 = requests.get(url="https://hq.sinajs.cn/list=%s"% code) 
    datas = str.split(r2.text,"\"")
    bbs = str.split(datas[1],",")
    detail ={}
    detail["name"]= bbs[0]
    detail["close"]= bbs[3]
    detail["open"]=bbs[1]
    detail["high"]=bbs[4]
    detail["low"]=bbs[5]
    detail['tdate'] =bbs[30] 
    return(detail)



# ktype k线级别 
# 101 日 
# 102 周 
# 103 月
# 是否保存图片
def calSkdj(ktype,savepic,realtime):
    conn = connect_db()
    sql = "SELECT  DISTINCT code FROM %s  " % TABLE_FUND_DAY_K_DATA
    df_fund_code = pd.read_sql(sql=sql,con=conn)
    codes = list(df_fund_code['code'])

    

    buyCodes =[]
    for code in tqdm.tqdm(codes):
        try:
            current =None
            sql = "SELECT  * FROM %s WHERE code= %s AND ktype=%s " % (TABLE_FUND_DAY_K_DATA, code,ktype)
            if realtime:
                current = getRealTimeFundData(code)
                sql = "SELECT  * FROM %s WHERE code= %s AND ktype=%s AND cast(tdate as datetime)<cast('%s' as datetime)" % (TABLE_FUND_DAY_K_DATA, code,ktype,current['tdate'])
            
            df_code = pd.read_sql(sql=sql, con=conn)
            size =len(df_code)
            df_code.loc[size] =pd.Series(current)
        

            LOWV = LOW(df_code, 9)
            HIGV = HIGH(df_code, 9)
            import numpy as np
            df_float = df_code['close'].astype('float')
            cal1 = (df_float - LOWV)
            cal2 = (HIGV - LOWV)
            cal1 = cal1 / cal2 * 100
            RSV = talib.EMA(cal1, 3)
            K = talib.EMA(RSV, 3)
            D = talib.MA(K, 3)
            df_cp = df_code.copy()

            df_cp.insert(df_cp.shape[1], 'K', K)
            df_cp.insert(df_cp.shape[1], 'D', D)
            df_cp = df_cp.dropna()

            canBuy =judeBuyPoint(ktype, df_cp)
            #talib.stream_CDLHARAMICROSS()
            if canBuy:
                buyCodes.append(code)



            if savepic and canBuy:
                df_cp2 = df_cp.rename(columns={"open": "Open", "close": "Close", "high": "High", "low": "Low"}).tail(60)
                df_cp2["Open"] = df_cp2["K"].astype("float")

                df_cp2["Close"] = df_cp2["D"].astype("float")
                df_cp2["High"] = df_cp2["D"].astype("float")
                df_cp2["Low"] = df_cp2["K"].astype("float")
                df_cp2['tdate'] = pd.to_datetime(df_cp2['tdate'])
                df_cp2.set_index("tdate", inplace=True)


                apdict = mpf.make_addplot(df_cp2[['D', 'K']])
                save =None
                if ktype == 101:
                    save = dict(fname='./tmp/%s日线SKDJ.jpg' % code, dpi=30, pad_inches=0.25)
                if ktype == 102:
                    save = dict(fname='./tmp/%s周线SKDJ.jpg' % code, dpi=30, pad_inches=0.25)
                if ktype == 103:
                    save = dict(fname='./tmp/%s月线SKDJ.jpg' % code, dpi=30, pad_inches=0.25)

                if save == None:
                    return

                mpf.plot(df_cp2, volume=False, addplot=apdict, type='candle', figscale=1.5, figratio=(4, 1), style="binance",
                         savefig=save)
        except Exception as e:
            continue
    return buyCodes





def createAndSaveBuyPointData(ktype,codes):
    conn = connect_db()
    TABLE_FUND_DAY_BUY_DATA ='shenge_money_buy_point_tmp'
    datas ={}
    year = range(len(codes))
    flag = range(len(codes))
    type = range(len(codes))
    ktypes = range(len(codes))
    datas['code'] = codes
    datas['year'] = year
    datas['flag'] = flag
    datas['type'] = type
    datas['ktype'] = ktypes
    df = pd.DataFrame(datas)
    df['year'] =time.strftime('%Y')
    if ktype==102:
        df['flag'] =time.strftime('%W')
    if ktype==101:
        df['flag'] =time.strftime('%Y-%m-%d')
    if ktype == 103:
        df['flag'] = time.strftime('%m')
    df['type'] ='SKDJ'
    df['ktype'] = ktype
    df.to_sql(TABLE_FUND_DAY_BUY_DATA, con=conn,  if_exists="append",index=False)

import os;

def deletePics():
    # out = os.system("del /q tmp\*")
    out = os.system("rm -rf tmp\*周线SKDJ.jpg")
    print(out)

def gitOper():
    print(os.system("git add -f tmp/*"))
    commit_msg = "auto commit SKDJ task picture：" + time.strftime("%Y-%m-%d %H:%M:%S") 
    print(os.system("git commit -m '%s'" % commit_msg))
    print(os.system("git push"))
   

deletePics()
bcodes = calSkdj(102,True,True)
createAndSaveBuyPointData(102,codes=bcodes)
gitOper()








